PHP MySQL Ajax Live Search
https://www.tutorialrepublic.com/php-tutorial/php-mysql-ajax-live-search.php
In this tutorial you'll learn how to create a live MySQL database search feature using PHP and Ajax.
Step 1: Creating the Database Table
Execute the following SQL query to create the countries table in your MySQL database.
CREATE TABLE countries (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL
);
After creating the table, you need to populate it with some data using the SQL INSERT statement.
Step 2: Creating the Search Form
Now, let's create a simple web interface that allows user to live search the names of countries available in our countries table, just like an autocomplete or typeahead.
Create a PHP file named "search-form.php" and put the following code inside of it.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>PHP Live MySQL Database Search</title>
<script src="https://code.jquery.com/jquery-1.12.4.min.js"></script>
<script>
$(document).ready(function(){
$('.search-box input[type="text"]').on("keyup input", function(){
/* Get input value on change */
var inputVal = $(this).val();
var resultDropdown = $(this).siblings(".result");
if(inputVal.length){
$.get("backend-search.php", {term: inputVal}).done(function(data){
// Display the returned data in browser
resultDropdown.html(data);
});
} else{
resultDropdown.empty();
}
});
// Set search input value on click of result item
$(document).on("click", ".result p", function(){
$(this).parents(".search-box").find('input[type="text"]').val($(this).text());
$(this).parent(".result").empty();
});
});
</script>
</head>
<body>
<div class="search-box">
<input type="text" autocomplete="off" placeholder="Search country..." />
<div class="result"></div>
</div>
</body>
</html>
Every time the content of search input is changed or keyup event occur on search input the jQuery code (line no-47 to 67) sent an Ajax request to the "backend-search.php" file which retrieves the records from countries table related to the searched term.
Those records later will be inserted inside a <div> by the jQuery and displayed on the browser.
Step 3: Processing Search Query in Backend
And here's the source code of our "backend-search.php" file which searches the database based on query string sent by the Ajax request and send the results back to browser.
Example
<?php
/* Attempt MySQL server connection.
Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("localhost", "root", "", "demo");
// Check connection
if($link === false){
die("ERROR: Could not connect." . mysqli_connect_error());
}
if(isset($_REQUEST["term"])){
// Prepare a select statement
$sql = "SELECT * FROM countries WHERE name LIKE ?";
if($stmt = mysqli_prepare($link, $sql)){
// Bind variables to the prepared statement as parameters
mysqli_stmt_bind_param($stmt, "s", $param_term);
// Set parameters
$param_term = $_REQUEST["term"] . '%';
// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmt)){
$result = mysqli_stmt_get_result($stmt);
// Check number of rows in the result set
if(mysqli_num_rows($result) > 0){
// Fetch result rows as an associative array
while($row = mysqli_fetch_array($result, MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}
// Close statement
mysqli_stmt_close($stmt);
}
// close connection
mysqli_close($link);
?>
<?php
/* Attempt MySQL server connection.
Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$mysqli = new mysqli("localhost", "root", "", "demo");
// Check connection
if($mysqli === false){
die("ERROR: Could not connect. " . $mysqli->connect_error);
}
if(isset($_REQUEST["term"])){
// Prepare a select statement
$sql = "SELECT * FROM countries WHERE name LIKE ?";
if($stmt = $mysqli->prepare($sql)){
// Bind variables to the prepared statement as parameters
$stmt->bind_param("s", $param_term);
// Set parameters
$param_term = $_REQUEST["term"] . '%';
// Attempt to execute the prepared statement
if($stmt->execute()){
$result = $stmt->get_result();
// Check number of rows in the result set
if($result->num_rows > 0){
// Fetch result rows as an associative array
while($row = $result->fetch_array(MYSQLI_ASSOC)){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
} else{
echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
}
// Close statement
$stmt->close();
}
// Close connection
$mysqli->close();
?>
<?php
/* Attempt MySQL server connection.
Assuming you are running MySQL
server with default setting (user 'root' with no password) */
try{
$pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
// Set the PDO error mode to exception
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e){
die("ERROR: Could not connect. " . $e->getMessage());
}
// Attempt search query execution
try{
if(isset($_REQUEST["term"])){
// create prepared statement
$sql = "SELECT * FROM countries WHERE name LIKE :term";
$stmt = $pdo->prepare($sql);
$term = $_REQUEST["term"] . '%';
// bind parameters to statement
$stmt->bindParam(":term", $term);
// execute the prepared statement
$stmt->execute();
if($stmt->rowCount() > 0){
while($row = $stmt->fetch()){
echo "<p>" . $row["name"] . "</p>";
}
} else{
echo "<p>No matches found</p>";
}
}
} catch(PDOException $e){
die("ERROR: Could not able to execute $sql. " . $e->getMessage());
}
// Close statement
unset($stmt);
// Close connection
unset($pdo);
?>
The SQL SELECT statement is used in combination with the LIKE operator (line no-16) to find the matching records in countries database table.
We've implemented the prepared statement for better search performance as well as to prevent the SQL injection attack.
Note: Always filter and validate user input before using it in a SQL statement.
You can also use PHP mysqli_real_escape_string() function to escape special characters in a user input and create a legal SQL string to protect against SQL injection.